<?php

App::uses('AppModel', 'Model');

class Production extends AppModel {
	
	public $useTable = "production";
	
	public $belongsTo = array('Store', 'Driver', 'Vehicle', 'Carrier');
	
	public $hasAndBelongsToMany = array( 'Product' );
	
	public $hasMany = array(
		'ProductionProduct' => array('dependent' => true),
		'ClientRebate' => array(
			'foreignKey' => false,
			'finderQuery' => 'SELECT ClientRebate.* 
							 FROM 
							 	client_rebates ClientRebate
							 	INNER JOIN stores s ON s.client_id=ClientRebate.client_id
							 	INNER JOIN production p ON p.store_id=s.id
							 WHERE 
							 	ClientRebate.effective_date<=p.date
							 	AND p.id = {$__cakeID__$}
							 ORDER BY ClientRebate.effective_date DESC
							 LIMIT 1'
		),
        'ClientPayingCurrency' => array(
            'foreignKey' => false,
            'finderQuery' => 'SELECT ClientPayingCurrency.* 
							 FROM 
							 	client_paying_currencies ClientPayingCurrency
							 	INNER JOIN stores s ON s.client_id=ClientPayingCurrency.client_id
							 	INNER JOIN production p ON p.store_id=s.id
							 WHERE 
							 	ClientPayingCurrency.effective_date<=p.date
							 	AND p.id = {$__cakeID__$}
							 ORDER BY ClientPayingCurrency.effective_date DESC
							 LIMIT 1'
        ),
	    'Tax' => array(
	        'foreignKey'  => false,
	        'finderQuery' => 'SELECT Tax.* 
							 FROM 
							 	taxes Tax
							 	INNER JOIN production p ON Tax.effective_date <= p.date
							 WHERE p.id = {$__cakeID__$}
							 ORDER BY Tax.effective_date DESC
							 LIMIT 1'
	    )
	);
	
	public $validate = array(
		'store_id' => array(
			'rule' => 'numeric',
			'required' => true,
			'message' => 'Morate odabrati prodavnicu!'
		)
	);
	
	public function getPricelist($onDate) {
		$sql = '
			SELECT pp1.product_id, pp2.effective_date, pp2.price
			FROM 
				product_prices pp1
				INNER JOIN (
					SELECT product_id, price, effective_date
					FROM product_prices
					WHERE effective_date <= "' . $onDate . '"
					ORDER BY effective_date DESC, id DESC
				) pp2 ON pp1.product_id=pp2.product_id
			GROUP BY pp1.`product_id`
		';
		if($result = $this->query($sql)) {
			$pricelist = array();
			foreach($result as $row) {
				$row['pp2']['price'] = (float)$row['pp2']['price'];
				$pricelist[$row['pp1']['product_id']] = $row['pp2'];
			}
			return $pricelist;
		}
		
		return array();
	}
    
    public function reportByProduct($filter) {
        $return = false;
        
        $date_start = $filter['date_start'];
        $date_end   = $filter['date_end'];
        $product_id = (int)$filter['product_id'];
        
        if (!empty($date_start) && !empty($date_end) && $product_id>0) {
            $q = "
                SELECT
                    s.id,
                    IF(s.name!='', TRIM(BOTH ' ' FROM s.name), 
                        CONCAT(
                            c.name, 
                            IF(s.address!='', CONCAT(', ',s.address), ''),
                            IF(s.township!='', CONCAT(', ',s.township), ''),
                            IF(s.city!='', CONCAT(', ',s.city), '')
                        )
                    ) as store
                FROM
                    production p
                    INNER JOIN production_products pp ON p.id=pp.production_id
                    INNER JOIN stores s ON p.store_id=s.id
                    INNER JOIN clients c ON c.id=s.client_id
                WHERE
                    pp.product_id = {$product_id} AND
                    p.date BETWEEN '{$date_start}' AND '{$date_end}'
                GROUP BY s.id
                ORDER BY store
                ";
            if ($stores = $this->query($q)) {
                $return = array();
                $types = array('otprema', 'povraćaj');
                
                foreach ($stores as $store) {
                    $store_id = $store['s']['id'];
                    $data = array( 'store' => array(
                        'id'   => $store_id,
                        'name' => $store[0]['store']
                    ));
                    
                    foreach($types as $type) {
                        $q = "
                            SELECT
                                p.store_id,
                                SUM(pp.amount) as amount,
                                SUM(pp.amount * pp.price * (1-pp.rabat/100)) as price
                            FROM
                                production p
                                INNER JOIN production_products pp ON p.id=pp.production_id
                            WHERE 
                                p.type='{$type}'
                                AND p.store_id={$store_id}
                                AND p.date BETWEEN '{$date_start}' AND '{$date_end}'
                                AND pp.product_id = {$product_id}
                        ";
                        list($result) = $this->query($q);
                        $data[$type] = array(
                            'amount' => (float)$result[0]['amount'],
                            'price' => (float)$result[0]['price']
                        );
                    }
                    $return[$store_id] = $data;
                }
            }
        }
        
        return $return;
    }
	
}
